package com.datasecops.scannerservices.watermark;

import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.ArrayList;

/**
 *Sqlserver数据库水印嵌入的相关操作
 *@author: GeXiaoLong
 *@time: 2023/2/7 17:54
 */
@Service
@Slf4j
public class SQLServerDB {
    @Resource
    private  ImportAndExport importAndExport;

    public Integer sqlServerToCsv(String name) throws SQLException, ClassNotFoundException, IOException {
        //使用Java JDBC连接数据库：
        //1、加载驱动
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        //2、建立连接
        String url="jdbc:sqlserver://10.26.18.67:1433;DatabaseName=dsm;encrypt=false";
        String user="root";
        String password="1234567a.";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //（2）构建SQL查询语句，查询出所需要的数据：
        //然后使用JDBC获取表的建表语句：
        String model = "text";
        String models = "soul";//用户默认创建的新库
        String tableName = name;
        //String sql = "SELECT * FROM " + "\"" + tableName + "\"";
        String sql = "SELECT * FROM " + model + "." + "\""+tableName+"\"";
        System.out.println(sql);
        //这个是表数据
        Statement stmt = conn.createStatement();


        //获取表的建表语句
     /*   String sqls = "select dbms_metadata.get_ddl('TABLE'," + "\'" + tableName + "\'" + ") from dual";//这个语句是获取到Oracle完整的建表语句
        System.out.println(sqls);
        ResultSet rss = stmt.executeQuery(sqls);
        String tableSql = null;
        while (rss.next()) {
            tableSql = rss.getString(1);
            System.out.println(tableSql);
            System.out.println("---------------------------------------------------------------------------------------");
        }*/



        //这个是表结构
        DatabaseMetaData metaData1 = conn.getMetaData();
        ResultSet pkInfo = metaData1.getPrimaryKeys(null, model, tableName);//这里的text后期要改成动态的
        System.out.println(pkInfo == null);
        StringBuilder surface = new StringBuilder("");
        String primary = null;
        // System.out.println(pkInfo.next());
        while (pkInfo.next()) {
            System.out.print("数据库名称:" + pkInfo.getString("TABLE_CAT") + "                  ");
            System.out.print("表名称:" + pkInfo.getString("TABLE_NAME") + "                  ");


            surface.append("CREATE TABLE");
            surface.append(" ");
            surface.append(pkInfo.getString("TABLE_NAME"));
            surface.append(" ");
            surface.append("(");
            surface.append("\n");


            System.out.print("主键列的名称:" + pkInfo.getString("COLUMN_NAME") + "                  ");
            primary = pkInfo.getString("COLUMN_NAME");
            System.out.print("类型:" + pkInfo.getString("PK_NAME") + "                  ");
            System.out.println("");
        }


        ResultSet tableInfo = metaData1.getColumns(null, model, tableName, null);
        while (tableInfo.next()) {
            // 表的名字
            System.out.print("表名:" + tableInfo.getString("TABLE_NAME") + "    ");
            // 列的名称
            System.out.print("列名:" + tableInfo.getString("COLUMN_NAME") + "    ");

            // 默认值
            System.out.print("默认值 :" + tableInfo.getString("COLUMN_DEF") + "  ");
            // 字段的类型
            System.out.print("字段的类型:" + tableInfo.getString("TYPE_NAME") + "   ");

            // 是否可以为空
            System.out.print("是否可以为空:" + tableInfo.getString("IS_NULLABLE") + "  ");

            // 是否为自增
            System.out.print("是否为自增:" + tableInfo.getString("IS_AUTOINCREMENT") + " ");
            // 字段说明
            System.out.print("字段说明:" + tableInfo.getString("REMARKS") + "  ");
            // 长度(有时候是错的)
            System.out.print("长度:" + tableInfo.getString("COLUMN_SIZE") + " ");
            surface.append(tableInfo.getString("COLUMN_NAME"));
            surface.append(" ");
            surface.append(tableInfo.getString("TYPE_NAME"));
            surface.append(" ");
            surface.append("(");
            if ("DATE".equalsIgnoreCase(tableInfo.getString("TYPE_NAME"))){
                surface.append(6);
            }else {
                surface.append(tableInfo.getString("COLUMN_SIZE"));
            }
            //surface.append(tableInfo.getString("COLUMN_SIZE"));
            surface.append(")");
            surface.append(" ");
            surface.append("COLLATE utf8mb4_unicode_ci");
            surface.append(" ");
            if ("NO".equalsIgnoreCase(tableInfo.getString("IS_NULLABLE"))) {
                surface.append("NOT NULL");
            } else {
                surface.append("DEFAULT NULL");
            }
            surface.append(" ");
            surface.append(",");
            surface.append("\n");
            System.out.println();
        }
        surface.append("PRIMARY KEY");
        surface.append(" ");
        surface.append("(");
        surface.append(primary);
        surface.append(")");
        surface.append(" ");
        surface.append("USING BTREE");
        surface.append("\n");
        surface.append(")");
        surface.append(" ");
        surface.append("ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        String s = surface.toString();
        String replace = null;
        replace = s.replace("nchar", "char");
        replace = replace.replace("nvarchar","varchar");
        replace = replace.replace("ntext","text");
        replace = replace.replace("image","blob");
        replace = replace.replace("bit","tinyint");

        System.out.println(replace);


        //（3）将查询结果写入CSV文件：
        ResultSet rs = stmt.executeQuery(sql);
        OutputStreamWriter fw = null;
        try {
            fw = new OutputStreamWriter(new FileOutputStream("/root/your_file.csv"),
                    StandardCharsets.UTF_8);
//            fw = new OutputStreamWriter(new FileOutputStream("P:\\text\\your_file.csv"),
//                    StandardCharsets.UTF_8);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
//        FileWriter fw = new FileWriter("P:\\text\\your_file.csv");
        ResultSetMetaData metaData = rs.getMetaData();
        int columeCount = metaData.getColumnCount();//
        String columnName = metaData.getColumnName(1);
        //String columnName = metaData.getColumnName(1);
        //System.out.println(columeCount);
        String String = "";

        for (int i = 1; i <= columeCount; i++) {
            if (!(i == columeCount)) {
                String = String + metaData.getColumnName(i) + ",";
            } else {
                String = String + metaData.getColumnName(i) + "\n";
            }
        }
        try {
            fw.append(String);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //String outputString = "";
        while (rs.next()) {
            String outputString = "";
            for (int i = 1; i <= columeCount; i++) {
                if (!(i == columeCount)) {
                    String str = rs.getString(i);
                    boolean b = rs.wasNull();
                    System.out.println(b);
                    int i1 = -1;
                    if (str != null) {
                        i1 = str.indexOf("\n");//如果获取到的字符串中含有换位符\n ,那么i1的值就不是-1,只有不包含的时候才是-1;
                    }
                    if ( i1 != -1){
                        String replaces = null;
                        if (str != null){
                            replaces = str.replace("\n", "");//把自动生成的换位符去掉
                        }
                        outputString = outputString + replaces + ",";
                    }else {
                        if (b){
                            outputString = outputString  + "NULL"+",";
                        }else {
                            outputString = outputString + rs.getString(i) + ",";
                        }
                        //outputString = outputString + rs.getString(i) + ",";
                    }
                } else {
                    outputString = outputString + rs.getString(i) + "\n";
                }
            }
            try {
                fw.append(outputString);
            } catch (IOException e) {
                e.printStackTrace();
            }

        }

        fw.flush();

        fw.close();

        //（4）关闭数据库连接：

        rs.close();

        stmt.close();

        conn.close();

        Integer integer = importData(replace, tableName, model, models);

        return integer;
    }

    /**
     *创建新表和导入csv
     * @Description
     * @author GeXiaoLong
     * @time 2023/1/15 11:27
     * @param
     * @param tableName
     * @param model
     * @param models
     * @return
     */
    public Integer importData(String surface, String tableName, String model, String models) throws SQLException,
            ClassNotFoundException {

        String loadDataQuery = " LOAD DATA LOCAL INFILE '/root/your_file.csv' INTO TABLE " +
                tableName + " FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES";

        log.error("数据库安装程序进来了吗?");
        Statement st = null;
        try {
            log.error("数据库安装程序运行进来了吗?");
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://10.26.18.58:3306/mysql?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai";
            String username = "root";
            String password = "Wzx.1104";
            Connection c = DriverManager.getConnection(url, username, password);
            st = c.createStatement();
            st.executeUpdate("CREATE DATABASE IF NOT EXISTS WatermarkingDatabase");//创建数据库
            st.executeUpdate("USE WatermarkingDatabase");
            ResultSet resultSet = st.executeQuery("SHOW TABLES LIKE 'SOULWJ'");
            while (resultSet.next()) {
                String string = resultSet.getString(1);
                System.out.println(string);
            }


            int i = st.executeUpdate(surface);//创建一张新表
            System.out.println(i);
            //rs = st.executeQuery("select * from student");
            System.out.println(loadDataQuery);

            //执行导入csv文件代码
            boolean execute = st.execute(loadDataQuery);
            int updateCount = st.getUpdateCount();
            while (!(updateCount > 0)) {
                System.out.println("正在导入");
            }
            System.out.println(execute);//导入csv文件
//System.out.println(loadDataQuery);
            //st.executeUpdate( loadDataQuery);
            //st.execute(loadDataQuery);
        } catch (Exception e) {
            log.error("数据库添加程序运行失败,原因是: ", e);
        } finally {
            st.close();
        }

        Integer watermarking = null;
        try {
            watermarking = watermarking(tableName, model, models);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return watermarking;
    }


    /**
     *创建加注水印后的新库
     * @Description
     * @author GeXiaoLong
     * @time 2023/2/4 14:10
     * @param
     * @param tableName
     * @param model
     * @param models
     * @return
     */
    public Integer watermarking(String tableName, String model, String models) throws SQLException, IOException,
            ClassNotFoundException {
        Integer text = importAndExport.text(tableName);

        //对比行数
        Integer integer = ReadCSV();//csv文件的行数
        Integer integer1 = DBRows(tableName);//数据库导入的行数
        ArrayList<String> list = new ArrayList<>();
        log.error("对比行数出来了吗?");
        while ((integer-1) == integer1){
            log.error("对比行数前进来了吗?");
            list = watermarkExport(tableName,models);
            break;
        }
        watermarkingImport(list);
        return text;
    }



    /**
     *水印加注完后的导出(用sql语句方法)
     * @Description
     * @author GeXiaoLong
     * @time 2023/2/6 15:34
     * @param
     * @param tableName
     * @param models
     * @return
     */
    public ArrayList<String> watermarkExport(String tableName, String models) throws ClassNotFoundException, IOException, SQLException {
        //使用Java JDBC连接数据库：
        //使用Java JDBC连接数据库：
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://10.26.18.58:3306/mysql?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai";
        String username = "root";
        String password = "Wzx.1104";


        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //（3）将查询结果写入sql语句中：
        Statement stmt = conn.createStatement();
        String mode = "watermarkingdatabase";
        //String tableName = "dlp_datarisksit_assessment_question_bank";
        String sql = "SELECT * FROM " + mode +"."+tableName;
        ResultSet rs = stmt.executeQuery(sql);
        StringBuilder stringBuilder = new StringBuilder("");
        ArrayList list = new ArrayList();
        ResultSetMetaData metaData = rs.getMetaData();
        int columeCount = metaData.getColumnCount();//
        String string = "";

        for (int i = 1; i <= columeCount; i++) {
            if (!(i == columeCount)) {
                string = string + "\""+ metaData.getColumnName(i) + "\"" + ",";
            } else {
                string = string + "\"" + metaData.getColumnName(i)+ "\"" ;
            }
        }
        //String outputString = "";

        while (rs.next()) {
            String data = "";
            String outputString = "";
            for (int i = 1; i <= columeCount; i++) {
                if (!(i == columeCount)) {
                    String str = rs.getString(i);
                    boolean b = rs.wasNull();
                    //System.out.println(b);
                    int i1 = -1;
                    if (str != null) {
                        i1 = str.indexOf("\n");//如果获取到的字符串中含有换位符\n ,那么i1的值就不是-1,只有不包含的时候才是-1;
                    }
                    if ( i1 != -1){
                        String replaces = null;
                        if (str != null){
                            replaces = str.replace("\n", "");//把自动生成的换位符去掉
                        }
                        outputString = outputString + "\'" + replaces + "\'" + ",";
                    }else {
                        if (b){
                            outputString = outputString  + "NULL" +",";
                        }else {
                            outputString = outputString + "\'" + rs.getString(i) + "\'" + ",";
                        }
                        //outputString = outputString + rs.getString(i) + ",";
                    }
                } else {
                    outputString = outputString + "\'" + rs.getString(i) +"\'";
                    stringBuilder.append("INSERT INTO ");
                    stringBuilder.append(models);
                    stringBuilder.append(".");
                    stringBuilder.append("\"");
                    stringBuilder.append(tableName);
                    stringBuilder.append("\"");
                    stringBuilder.append("(");
                    stringBuilder.append(string);
                    stringBuilder.append(")");
                    stringBuilder.append(" VALUES ");
                    stringBuilder.append("(");
                    stringBuilder.append(outputString);
                    stringBuilder.append(")");
                    //stringBuilder.append(";");
                    stringBuilder.append("\n");
                    data = stringBuilder.toString();
                    System.out.println(data);
                    list.add(data);
                    stringBuilder.setLength(0);//这是把stringBuilder的内容清空



                }
            }
            //           String outputString = rs.getString(1) + "," + rs.getString(2) + "," + rs.getString(3) + "\n";

            //stringBuilder.append(outputString);

        }

        //（4）关闭数据库连接：
        rs.close();
        conn.close();

        return list;
    }



    /**
     *获取csv文件的行数
     * @Description
     * @author GeXiaoLong
     * @time 2023/2/6 15:45
     * @param
     * @return
     */
    public Integer ReadCSV() {
        int count = 0;
        try {
            FileReader fr = new FileReader("/root/your_file.csv");
            BufferedReader br = new BufferedReader(fr);
            //count = 0;
            while (br.readLine() != null) {
                count++;
            }
            System.out.println("行数：" + count);
            br.close();
            fr.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return count;
    }

    /**
     *数据库导入的行数
     * @Description
     * @author GeXiaoLong
     * @time 2023/2/6 16:34
     * @param
     * @param tableName
     * @return
     */
    public Integer DBRows(String tableName) throws ClassNotFoundException, SQLException {
        //使用Java JDBC连接数据库：
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://10.26.18.58:3306/mysql?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai";
        String username = "root";
        String password = "Wzx.1104";
        String mode = "watermarkingdatabase";

        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Statement stmt = conn.createStatement();
        //String sql = "SELECT COUNT(*) FROM ROOT.dlp_datarisksit_assessment_question_bank";

        String sql = "SELECT COUNT(*) FROM " + mode + "." + tableName;

        ResultSet rs = stmt.executeQuery(sql);
        int count = 0;
        if (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }


    /**
     *加注完水印后导回源库
     * @Description
     * @author GeXiaoLong
     * @time 2023/2/6 16:38
     * @param
     * @param sql
     * @return
     */
    public void watermarkingImport(ArrayList<String> sql) throws SQLException {

        log.error("数据库安装程序进来了吗?");
        Statement stmt = null;
        try {
            log.error("数据库安装程序运行进来了吗?");
            //1、加载驱动
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            //2、建立连接
            String url="jdbc:sqlserver://10.26.18.67:1433;DatabaseName=dsm;encrypt=false";
            String user="root";
            String password="1234567a.";

            Connection conn = DriverManager.getConnection(url, user, password);
            System.out.println(sql);
            stmt = conn.createStatement();
            for (String o : sql) {
                //System.out.println(o);
                int i = stmt.executeUpdate(o);
                //System.out.println(i);
            }
            // int i = stmt.executeUpdate(sql);
            //System.out.println(i);
        } catch (Exception e) {
            log.error("数据库添加程序运行失败,原因是: ",e);
        } finally {
            stmt.close();
        }

        log.error("加注水印的程序进来了吗3");
//        OracleDB oracleDB = new OracleDB();
//        oracleDB.watermarking();
    }





    public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
        SQLServerDB sqlServerDB = new SQLServerDB();
        String model = "text";
        String models = "soul";
        String tableName = "dlp_datarisksit_assessment_question_bank";
        //ArrayList<String> list = sqlServerDB.watermarkExport(tableName, model, models);
        //sqlServerDB.watermarkingImport(list);
        //sqlServerDB.sqlServerToCsv(name);
    }
}
